package cn.net.autocode.platform.setting;

import cn.hutool.core.util.StrUtil;
import cn.net.autocode.dbManager.Dao;
import cn.net.autocode.dbManager.IdWorker;
import cn.net.autocode.platform.cache.EhCacheUtil;
import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.ehcache.Cache;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;
import java.util.*;

@Slf4j
@Service
@Component
public class MetadataHelper {

    private final Dao dao;
    private Cache<String,String> formQuerySqlCache;

    public MetadataHelper(Dao dao) {
        this.dao = dao;
    }

    public FormData paramDeal(JSONObject param, String formCode) {
        List<String> removeKeys = new ArrayList<>();
        //获取所有元数据
        JSONObject sysForm = dao.queryJSON("select * from sys_form where code=?",formCode);
        String mainTable = sysForm.getString("mainTable");
        String refColumn =  sysForm.getString("refColumn");

        JSONArray metadatas = dao.queryJSONArray("select t2.* from sys_form t1,sys_form_metadata t2 where t1.id=t2.form_id and t1.code=?",formCode);
        JSONObject mainJson = new JSONObject();
        JSONObject extTable = new JSONObject();
        //处理主表JSON
        for(int i=0;i<metadatas.size();i++){
            JSONObject metaData = metadatas.getJSONObject(i);
            String fieldName = metaData.getString("fieldName");
            String refTable = metaData.getString("refTable");
            if(StrUtil.isNotBlank(refTable) && StrUtil.isNotBlank(fieldName)  && param.containsKey(fieldName)){
                if (mainTable.equals(refTable)) {
                    mainJson.put(fieldName, param.get(fieldName));
                }else{
                    if (extTable.containsKey(refTable)) {
                        extTable.getJSONObject(refTable).put(fieldName, param.get(fieldName));
                    }else{
                        JSONObject extData = new JSONObject();
                        extData.put(fieldName, param.get(fieldName));
                        extTable.put(refTable, extData);
                    }
                }
                removeKeys.add(fieldName);
            }
        }

        //处理未知的数据
        param.keySet().forEach(key->{
            if(!removeKeys.contains(key)){
                mainJson.put(key,param.get(key));
            }
        });

        FormData formData = new FormData();
        formData.setMainTable(mainTable);
        formData.setRefColumn(refColumn);
        formData.setMainData(mainJson);
        formData.setExtData(extTable);
        return formData;
    }

    public String querySql(String formCode) {

        if(this.formQuerySqlCache==null){
            this.formQuerySqlCache = EhCacheUtil.createCache("SYS_FORM_QUERY_SQL", String.class,600);
        }

        if(this.formQuerySqlCache.containsKey(formCode)){
            return this.formQuerySqlCache.get(formCode);
        }

            //column

        //获取所有元数据
        JSONObject sysForm = dao.queryJSON("select * from sys_form where code=?",formCode);
        String mainTable = sysForm.getString("mainTable");
        String refColumn =  sysForm.getString("refColumn");

        JSONArray metadatas = dao.queryJSONArray("select t2.* from sys_form t1,sys_form_metadata t2 where t1.id=t2.form_id and t1.code=?",formCode);
        Map<String, List<String>> extTable = new HashMap<>();
        //处理主表JSON
        for(int i=0;i<metadatas.size();i++){
            JSONObject metaData = metadatas.getJSONObject(i);
            String columnName = metaData.getString("columnName");
            String refTable = metaData.getString("refTable");

            if(StrUtil.isNotBlank(refTable) && StrUtil.isNotBlank(columnName) && !mainTable.equals(refTable)){
                if (extTable.containsKey(refTable)) {
                    extTable.get(refTable).add(columnName);
                }else{
                    List<String> extData = new ArrayList<>();
                    extData.add(columnName);
                    extTable.put(refTable, extData);
                }
            }

        }

        //处理未知的数据
        StringBuilder columnBuilder = new StringBuilder();
        StringBuilder tableBuilder = new StringBuilder();
        //处理主表
        columnBuilder.append("t.*");
        tableBuilder.append(mainTable+" t");

        //处理关联表
        int index = 1;
        Set<String> keySet = extTable.keySet();
        for (String key : keySet) {
            tableBuilder.append(" left join "+key+" t"+ index+" on t.id=t"+index+"."+refColumn);
            List<String> columns = extTable.get(key);
            for(String column:columns){
                columnBuilder.append(","+"t"+ index +"."+column);
            }
            index++;
        }


        String querySql = "select "+columnBuilder.toString()+" from "+tableBuilder.toString()+" where t.id=?";
        this.formQuerySqlCache.put(formCode,querySql);
        return querySql;
    }

    public String updateFields(JSONObject param,String formCode) {

        JSONObject sysForm = dao.queryJSON("select * from sys_form where code=?",formCode);
        String mainTable = sysForm.getString("mainTable");
        String refColumn =  sysForm.getString("refColumn");
        String formId = sysForm.getString("id");
        String mainTableId = param.getString("id");
        Set<String> keySet = param.keySet();
        keySet.remove("id");
        if (!keySet.isEmpty()){
            JSONArray metadataArray = dao.queryJSONArray("select * from sys_form_metadata where form_id=? and field_name in (?) and ref_table is not null and ref_table<>?",formId,keySet,mainTable);

            for (int i = 0; i < metadataArray.size(); i++) {
                JSONObject metadata = metadataArray.getJSONObject(i);
                String refTable = metadata.getString("refTable");
                String columnName = metadata.getString("columnName");
                String fieldName = metadata.getString("fieldName");
                String updateSql = "update "+refTable+" set "+columnName+"=? where "+refColumn+"=?";
                int updateNum = dao.executeUpdate(updateSql, param.get(fieldName), mainTableId);
                if (updateNum == 0){
                    String insertSql = "insert into "+refTable+" (id,"+columnName+","+refColumn+") values (?,?,?)";
                    dao.executeUpdate(insertSql, IdWorker.ulid(),param.get(fieldName), mainTableId);
                }
                param.remove(fieldName);
            }
        }


        //处理主表信息
        if (!param.isEmpty()){
            param.put("id",mainTableId);
            dao.updateJSON(param,mainTable);
        }

        return "success";
    }

}
